package org.liukai.tutorial.dao.imp;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.liukai.tutorial.dao.IAccountDao;
import org.liukai.tutorial.domain.Authority;
import org.liukai.tutorial.domain.Role;
import org.liukai.tutorial.domain.User;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository("accountDao")
public class AccountDao implements IAccountDao {

	private SimpleJdbcTemplate jdbcTemplate;

	@Resource(name = "dataSource")
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new SimpleJdbcTemplate(dataSource);
	}

	public User getUserByLoginName(String loginName) {

		String sql = "SELECT id,email,login_name,name,password FROM acct_user WHERE login_name = ?";

		List<User> list = jdbcTemplate.query(sql,
				new BeanPropertyRowMapper<User>(User.class),
				new Object[] { loginName });

		if (list.size() == 0) {
			return new User();
		}

		return list.get(0);
	}

	public List<Role> getRoleByUserName(String userName) {
		String sql = "SELECT acct_role.id,acct_role.name FROM acct_user "
				+ "LEFT JOIN acct_user_role ON acct_user_role.user_id=acct_user.id  "
				+ "LEFT JOIN acct_role ON acct_role.id=acct_user_role.role_id  "
				+ "WHERE acct_user.login_name = ?";
		return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Role>(
				Role.class), new Object[] { userName });
	}

	public List<Authority> getAuthorityByRole(Integer roleId) {
		String sql = "SELECT acct_authority.id,acct_authority.name,acct_authority.url "
				+ "FROM acct_authority LEFT JOIN acct_role_authority"
				+ " ON acct_authority.id=acct_role_authority.authority_id LEFT JOIN acct_role"
				+ " ON acct_role.id=acct_role_authority.role_id WHERE acct_role.id= ?";
		return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Authority>(
				Authority.class), new Object[] { roleId });
	}

	public List<User> getAllUser() {
		String sql = "SELECT id,email,login_name,name,password FROM acct_user";
		return jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(
				User.class));
	}

	public User getUserById(Integer id) {
		String sql = "SELECT id,email,login_name,name,password FROM acct_user WHERE id = ?";
		return jdbcTemplate.queryForObject(sql,
				new BeanPropertyRowMapper<User>(User.class),
				new Object[] { id });
	}

	public List<Role> getAllRole() {
		String sql = "SELECT id ,name FROM acct_role";
		return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Role>(
				Role.class));
	}

	public Role getRoleById(Integer id) {
		String sql = "SELECT id ,NAME FROM acct_role WHERE id = ?";
		return jdbcTemplate.queryForObject(sql,
				new BeanPropertyRowMapper<Role>(Role.class),
				new Object[] { id });
	}

	public int deleteUser(Integer id) {
		String sql = "DELETE FROM acct_user WHERE id = ?";
		return jdbcTemplate.update(sql, new Object[] { id });
	}

	public int deleteUserRoleByUserId(Integer id) {
		String sql = "DELETE FROM acct_user_role WHERE user_id = ?";
		return jdbcTemplate.update(sql, new Object[] { id });
	}

	public int deleteRoleUserByRoleId(Integer id) {
		String sql = "DELETE FROM acct_user_role WHERE role_id = ?";
		return jdbcTemplate.update(sql, new Object[] { id });
	}

	public int addUser(User user) {
		String sql = "INSERT INTO acct_user(email,login_name,name,password)"
				+ "VALUES(:email,:login_name,:name,:password)";
		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("email", user.getEmail());
		parameters.put("login_name", user.getLoginName());
		parameters.put("name", user.getName());
		parameters.put("password", user.getPassword());
		return jdbcTemplate.update(sql, parameters);
	}

	public int[] addUserRole(final Integer userId, final Integer[] roleMenus) {
		String sql = "INSERT INTO acct_user_role (user_id,role_id)VALUES(?,?)";

		List<Object[]> batch = new ArrayList<Object[]>();

		for (Integer roleMenu : roleMenus) {
			Integer[] values = new Integer[] { userId, roleMenu };
			batch.add(values);
		}
		return jdbcTemplate.batchUpdate(sql, batch);
	}

	public int updateUser(User user) {
		String sql = "UPDATE acct_user SET email=:email,login_name=:login_name,name=:name,password=:password WHERE id = :id ";
		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("email", user.getEmail());
		parameters.put("login_name", user.getLoginName());
		parameters.put("name", user.getName());
		parameters.put("password", user.getPassword());
		parameters.put("id", user.getId());
		return jdbcTemplate.update(sql, parameters);
	}

	public List<Authority> getAllAuthority() {
		String sql = "SELECT id,NAME,url FROM acct_authority ";
		return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Authority>(
				Authority.class));
	}

	public int addRole(Role role) {
		String sql = "INSERT INTO acct_role (name)VALUES(:name)";
		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("name", role.getName());
		return jdbcTemplate.update(sql, parameters);
	}

	public int[] addRoleAuthority(Integer id, Integer[] authMenus) {

		String sql = "";
		List<Object[]> batch = new ArrayList<Object[]>();

		if (id != null) {
			sql = "INSERT INTO acct_role_authority (role_id,authority_id)VALUES(?,?)";
			for (Integer authMenu : authMenus) {
				Integer[] values = new Integer[] { id, authMenu };
				batch.add(values);
			}
		} else {
			// 当id为null时,获得最新的自增列ID
			sql = "INSERT INTO acct_role_authority (role_id,authority_id)VALUES(LAST_INSERT_ID(),?)";
			for (Integer authMenu : authMenus) {
				Integer[] values = new Integer[] { authMenu };
				batch.add(values);
			}
		}

		return jdbcTemplate.batchUpdate(sql, batch);
	}

	public int deleteRole(Integer id) {
		String sql = "DELETE FROM acct_role WHERE id = ?";
		return jdbcTemplate.update(sql, new Object[] { id });
	}

	public int deleteRoleAuthority(Integer id) {
		String sql = "DELETE FROM acct_role_authority WHERE role_id = ?";
		return jdbcTemplate.update(sql, new Object[] { id });
	}

	public int updateRole(Role role) {
		String sql = "UPDATE acct_role SET name=:name WHERE id = :id ";
		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("name", role.getName());
		parameters.put("id", role.getId());
		return jdbcTemplate.update(sql, parameters);
	}

}
